package com.zdsoft.crm.dao.product;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.zdsoft.crm.entity.product.Product;
import com.zdsoft.crm.entity.product.ProductType;
import com.zdsoft.crm.vo.product.ProductListVo;

public interface IProductMapper {

	@Select("SELECT TL.ID id,TL.PRODUCTNAME productName,TL.PRODUCT_TYPE_ID product_type_id"
			+ ",TL.CREATE_TIME create_time,TL.PRODUCT_URL product_URL"
			+ ",TL.TEAM team,TL.COST_PRICE cost_price,TL.LIST_PRICE list_price"
			+ ",TL.MASTER_MAP master_map,TL.PRODUCT_INFO product_info"
			+ ",TL.PICTURE picture FROM("
			+ "SELECT A.*,ROWNUM RN "
			+ "FROM "
			+ " (SELECT * FROM PRODUCT ) A"
			+ " WHERE ROWNUM<=#{endIndex})  TL "
			+ " WHERE RN > #{startIndex}")
	List<ProductListVo> pageList(@Param("startIndex") Integer startIndex, @Param("endIndex") Integer endIndex);
	
	@Select("<script>SELECT count(id) FROM PRODUCT" + " where 1=1"
			+ " <if test='product_type_id != \"\"'> and PRODUCT_TYPE_ID=#{product_type_id}</if></script>")
	Long totalCount1(@Param("product_type_id") String product_type_id);
	
	@Select("<script>"
			+" select * from   (       "
			+ "  	select A.* ,ROWNUM rn"
			+ "		from ("
			+ "				select pr.ID id,pr.PRODUCTNAME productName,pr.PRODUCT_TYPE_ID"
			+ " product_type_id,pr.CREATE_TIME create_time,pr.PRODUCT_URL"
			+ " product_URL,pr.TEAM team,pr.COST_PRICE cost_price,pr.LIST_PRICE"
			+ " list_price,pr.MASTER_MAP master_map,pr.PRODUCT_INFO"
			+ " product_info,pr.PICTURE picture from PRODUCT pr"
			+ "            	<where>"
			+ "               	 	<if  test='product_type_id != null and product_type_id!=\"\"' >"
			+"								AND PRODUCT_TYPE_ID=#{product_type_id}"
			+ "					</if>"
			+ "            	</where> "
			+ "				) A"
			+ "		<if test='endIndex != null and endIndex!=\"\"' >"
			+ " 			<![CDATA[where ROWNUM <=#{endIndex}]]>"
			+ "		</if>  )"
			+ " <if test='beginIndex != null and beginIndex!=\"\"' >"
			+ " 		Where rn > #{beginIndex}"
			+ " </if>"
			+"</script>")
	List<ProductListVo> pageList1(@Param("beginIndex")int beginIndex, @Param("endIndex")int endIndex,@Param("product_type_id") String product_type_id);
	
	
	@Select("select count(id) from PRODUCT")
	Long totalCount();
	/**
	 * mybatis动态insert
	 * @param product
	 */
	@Insert("<script>"
			+ "insert into PRODUCT"
			+ "		(PRODUCTNAME,PRODUCT_TYPE_ID,CREATE_TIME,PRODUCT_URL,TEAM,COST_PRICE,LIST_PRICE,PRODUCT_INFO"
			+ "			<if test='master_map != null and master_map != \"\" '>"
			+ "				,MASTER_MAP "
			+ "			</if>"
			+ "			<if test='picture != null and picture != \"\" '>"
			+ "				,PICTURE"
			+ "			</if>"
			+ "		) values(#{productName},#{product_type_id},#{create_time},#{product_URL},#{team},#{cost_price},#{list_price},#{product_info}"
			+ "			<if test='master_map != null and master_map != \"\" '>"
			+ "				,#{master_map}"
			+ "			</if>"
			+ "			<if test='picture != null and picture != \"\" '>"
			+ "				,#{picture}"
			+ "			</if>"
			+ "		)"
			+ "</script>")
	void save(Product product);
	
	@Delete("delete from PRODUCT where id = #{id}")
	int deleteAll(String id);
	
	@Delete("delete from PRODUCT_TYPE where id = #{id}")
	int deleteTypeAll(String id);
	
	@Select("select * from PRODUCT where id = #{id}")
	Product getProductInfo(String id);
	
	@Select("select p.PRODUCTNAME productName,p.PRODUCT_TYPE_ID product_type_id"
			+ ",p.CREATE_TIME create_time,p.PRODUCT_URL product_URL"
			+ ",p.TEAM team,p.COST_PRICE cost_price,p.LIST_PRICE list_price"
			+ ",p.MASTER_MAP master_map,p.PRODUCT_INFO product_info"
			+ ",p.PICTURE picture from PRODUCT p where ID=#{id}")
	Product finInfo(String id);
	
	@Select("select pt.ID id,pt.TYPE_NAME typeName,pt.TYPE_INFO typeInfo from PRODUCT_TYPE pt where ID=#{id}")
	ProductType findTypeInfo(String id);
	
	@Select("select * from PRODUCT")
	List<Product> getProAll();
	
	@Update("<script>"
			+ "update PRODUCT set PRODUCTNAME=#{productName,jdbcType=VARCHAR}"
			+ ",PRODUCT_TYPE_ID=#{product_type_id,jdbcType=VARCHAR},CREATE_TIME=#{create_time}"
			+ ",PRODUCT_URL=#{product_URL,jdbcType=VARCHAR},TEAM=#{team,jdbcType=VARCHAR}"
			+ ",COST_PRICE=#{cost_price,jdbcType=VARCHAR},LIST_PRICE=#{list_price,jdbcType=VARCHAR}"
			+ "		<if test='master_map != null and master_map != \"\" '>"
			+ "			,MASTER_MAP=#{master_map,jdbcType=VARCHAR}"
			+ "		</if>"
			+ ",PRODUCT_INFO=#{product_info,jdbcType=VARCHAR}"
			+ "		<if test='picture != null and picture != \"\" '>"
			+ "			,PICTURE=#{picture,jdbcType=VARCHAR}"
			+ "		</if>"
			+ " where ID=#{id}"
			+ "</script>")
	void update(Product product);
	
	@Insert("insert into PRODUCT_TYPE(TYPE_NAME,TYPE_INFO) values(#{typeName}"
			+ ",#{typeInfo})")
	void saveType(ProductType productType);
	
	@Update("update PRODUCT_TYPE set TYPE_NAME=#{typeName},TYPE_INFO=#{typeInfo}"
			+ " where ID=#{id}")
	void updateType(ProductType productType);
	
	@Select("select pt.ID id from PRODUCT_TYPE pt where TYPE_NAME=#{typeName}")
	ProductType findByTypeName(String typeName);
	
	@Select("select p.ID id,p.TYPE_NAME typeName,p.TYPE_INFO typeInfo from PRODUCT_TYPE p")
	public List<ProductType>findAll();
	
	@Select("<script>SELECT count(id) FROM PRODUCT_TYPE" + " where 1=1"
			+ " <if test='typeName != \"\"'> and TYPE_NAME=#{typeName}</if></script>")
	Long totalCountType(@Param("typeName") String typeName);
	
	@Select("<script>"
			+" select * from   (       "
			+ "  	select A.* ,ROWNUM rn"
			+ "		from ("
			+ "				select p.ID id,p.TYPE_NAME typeName from PRODUCT_TYPE p"
			+ "            	<where>"
			+ "               	 	<if  test='typeName != null and typeName!=\"\"' >"
			+"								AND TYPE_NAME like '%'||#{typeName}||'%' "
			+ "					</if>"
			+ "            	</where> "
			+ "				) A"
			+ "		<if test='endIndex != null and endIndex!=\"\"' >"
			+ " 			<![CDATA[where ROWNUM <=#{endIndex}]]>"
			+ "		</if>  )"
			+ " <if test='beginIndex != null and beginIndex!=\"\"' >"
			+ " 		Where rn > #{beginIndex}"
			+ " </if>"
			+"</script>")
	List<ProductType> findPageType(@Param("beginIndex")int beginIndex, @Param("endIndex")int endIndex,@Param("typeName") String typeName);
}
